#coding=utf-8
from uliweb import expose
from uliweb.orm import do_

#可以省略直接引用
from uliweb import request, error, redirect
import simplejson
from sqlalchemy import desc,and_,or_
from sqlalchemy.sql import select, func

def __begin__():
    """
    用户验证 权限验证
    """
    from uliweb import functions
    functions.require_login()
    return functions.has_role()

@expose('/devicessearch')
class Search(object):

    def __init__(self):
        from uliweb.orm import get_model
        self.hardwarestatusmodel = get_model('hardwarestatus')
        self.hardwaremodel = get_model('hardware')
        self.hardwaretypemodel = get_model('hardwaretype')
        self.servermodel = get_model('server')        
        self.hardwarewarrantymodel = get_model('hardwarewarranty')
        self.rackspacemodel = get_model('rackspace')
        self.computerroommodel = get_model('computerroom')
        self.switchmodel = get_model('switch')
        self.switchipmodel = get_model('switchip')
        self.switchportmodel = get_model('switchport')
        self.servercpumodel = get_model('servercpu')
        self.servermemmodel = get_model('servermem')
        self.serverdiskmodel = get_model('serverdisk')
        self.serverpowermodel = get_model('serverpower')
        self.serverethmodel = get_model('serverethernet')
        self.serverethipmodel = get_model('serverethernetip')
        self.ipaddrmodel = get_model('ipaddress')
        self.serversystemmodel = get_model('serversystem')
        self.opersgroupprojectmodel = get_model('opersgroupproject')
        self.hardwaremanufacturermodel = get_model('hardwaremanufacturer')
    
    def server(self):
        """
        返回给jquery easyui的datagride

        所有设备的数据
        """
        pageIndex= int(request.POST.get('page',0))
        pageSize = int(request.POST.get('rows',20))

        #排序使用
        order_by_type = request.POST.get('order','asc')
        order_by_sort = request.POST.get('sort','id')
        order_by = order_by_sort + ' ' + order_by_type 
        
        action = request.GET.get('action')
        #精确搜索
        search_type = request.POST.get('search_type')        
        search_value = request.POST.get('search_value')
        #模糊搜索
        cr_name = request.POST.get('cr_name')
        rackspace_name = request.POST.get('rackspace_name')
        server_manufacturer = request.POST.get('server_manufacturer')
        server_type = request.POST.get('server_type')
        serverdisk_num = request.POST.get('serverdisk_num')
        serverdisk_size = request.POST.get('serverdisk_size')
        serverdisk_speed = request.POST.get('serverdisk_speed')
        servercpu_type = request.POST.get('servercpu_type')
        servercpu_hz = request.POST.get('servercpu_hz')
        servercpu_num = request.POST.get('servercpu_num')     
        servermem_type = request.POST.get('servermem_type')
        servermem_hz = request.POST.get('servermem_hz')
        servermem_size = request.POST.get('servermem_size')
        servermem_num = request.POST.get('servermem_num')
        #内存总容量
        servermemall_size = request.POST.get('servermemall_size')
        r1 = request.POST.get('r1')
        
        if pageIndex == 0:
            pageIndex = 1
            content = 0
        else:
            content = pageSize * (pageIndex - 1)

        data = {}
        data['rows'] = []

       #精确搜索
        if action == 'precise':
            if request.user.is_superuser:
                if search_type == "ethernet_ip":
                    #IP地址搜索
                    data['total'] = self.servermodel.filter(
                        self.serverethipmodel.c.server_id==self.servermodel.c.id
                                    ).filter(self.serverethipmodel.c.ethernet_ip==search_value.strip()).count()
        
                    data_info = {'data':self.servermodel.filter(
                                                  self.serverethipmodel.c.server_id==self.servermodel.c.id
                                                      ).filter(self.serverethipmodel.c.ethernet_ip==search_value.strip()
                                                               ).group_by(self.servermodel.c.server_sn
                                                          ).limit(pageSize).offset(content)
                                                 }
                elif search_type == "system_hostname":
                    #主机名搜索
                    data['total'] = self.servermodel.filter(
                        self.serversystemmodel.c.server_id==self.servermodel.c.id
                                    ).filter(self.serversystemmodel.c.system_hostname==search_value.strip()).count()
        
                    data_info = {'data':self.servermodel.filter(
                                                  self.serversystemmodel.c.server_id==self.servermodel.c.id
                                                      ).filter(self.serversystemmodel.c.system_hostname==search_value.strip()
                                                               ).group_by(self.servermodel.c.server_sn
                                                          ).limit(pageSize).offset(content)
                                 }
                else:
                    
                    data['total'] = self.servermodel.filter(and_(search_type + "='" + search_value.strip() + "'")).count()
                    data_info = {'data':self.servermodel.filter(and_(search_type + "='" + search_value.strip() + "'")).limit(1)
                                 }
            else:
                #非管理员只能查看自己项目所在的设备
                if search_type == "ethernet_ip":
                    #IP地址搜索
                    data['total'] = self.servermodel.filter(
                        self.serverethipmodel.c.server_id==self.servermodel.c.id
                                    ).filter(self.serverethipmodel.c.ethernet_ip==search_value.strip()
                                             ).filter(self.serversystemmodel.c.server_id ==self.servermodel.c.id
                                                ).filter(
                                                self.serversystemmodel.c.opersgroupproject_id == self.opersgroupprojectmodel.c.id
                                                ).filter(
                                                    and_(
                                                        str(request.user) + " in (opersgroupproject.ogproject_user, \
                                                                         opersgroupproject.ogproject_opuser)")
                                                ).count()
        
                    data_info = {'data':self.servermodel.filter(
                                                  self.serverethipmodel.c.server_id==self.servermodel.c.id
                                                      ).filter(self.serverethipmodel.c.ethernet_ip==search_value.strip()
                                                               ).filter(self.serversystemmodel.c.server_id ==self.servermodel.c.id
                                                        ).filter(
                                                            self.serversystemmodel.c.opersgroupproject_id == self.opersgroupprojectmodel.c.id
                                                                 ).filter(
                                                                     and_(
                                                                         str(request.user) + " in (opersgroupproject.ogproject_user, \
                                                                         opersgroupproject.ogproject_opuser)")
                                                                     ).group_by(self.servermodel.c.server_sn
                                                          ).limit(pageSize).offset(content)
                                                 }
                    
                elif search_type == "system_hostname":
                    #主机名搜索
                    data['total'] = self.servermodel.filter(
                        self.serversystemmodel.c.server_id==self.servermodel.c.id
                                    ).filter(self.serversystemmodel.c.system_hostname==search_value.strip()
                                             ).filter(
                                                self.serversystemmodel.c.opersgroupproject_id == self.opersgroupprojectmodel.c.id
                                                ).filter(
                                                    and_(
                                                        str(request.user) + " in (opersgroupproject.ogproject_user, \
                                                                         opersgroupproject.ogproject_opuser)")
                                                ).count()
        
                    data_info = {'data':self.servermodel.filter(
                                                  self.serversystemmodel.c.server_id==self.servermodel.c.id
                                                      ).filter(self.serversystemmodel.c.system_hostname==search_value.strip()
                                                               ).filter(
                                                            self.serversystemmodel.c.opersgroupproject_id == self.opersgroupprojectmodel.c.id
                                                                 ).filter(
                                                                     and_(
                                                                         str(request.user) + " in (opersgroupproject.ogproject_user, \
                                                                         opersgroupproject.ogproject_opuser)")
                                                                     ).group_by(self.servermodel.c.server_sn
                                                          ).limit(pageSize).offset(content)
                                                 }
                else:

                    data['total'] = self.servermodel.filter(self.serversystemmodel.c.server_id ==self.servermodel.c.id
                                                        ).filter(
                                                            self.serversystemmodel.c.opersgroupproject_id == self.opersgroupprojectmodel.c.id
                                                                 ).filter(
                                                                     and_(
                                                                         str(request.user) + " in (opersgroupproject.ogproject_user, \
                                                                         opersgroupproject.ogproject_opuser)")
                                                                     ).filter(and_(search_type + "='" + search_value.strip() + "'")).count()
                    data_info = {'data':self.servermodel.filter(self.serversystemmodel.c.server_id ==self.servermodel.c.id
                                                        ).filter(
                                                            self.serversystemmodel.c.opersgroupproject_id == self.opersgroupprojectmodel.c.id
                                                                 ).filter(
                                                                     and_(
                                                                         str(request.user) + " in (opersgroupproject.ogproject_user, \
                                                                         opersgroupproject.ogproject_opuser)")
                                                                     ).filter(and_(search_type + "='" + search_value.strip() + "'")).limit(1)
                                 }                    
                
        #模糊搜索
        elif action == 'fuzzy':
            if r1 == None or r1 == "":
                r1 = 'and'
            
            d = []
            if cr_name != "":
                d.append("computerroom.cr_name like '%%" + cr_name + "%%'")
            if rackspace_name != "":
                d.append("rackspace.rackspace_name like '%%" + rackspace_name + "%%'")
            if server_manufacturer != "":
                d.append("hardwaremanufacturer.hardwaremanufacturer_name like '%%" + server_manufacturer + "%%'")
            if server_type != "":
                d.append("hardware.hardware_name like '%%" + server_type + "%%'")
            if serverdisk_num != "":
                d.append("serverdisk.serverdisk_num =" + serverdisk_num + "")
            if serverdisk_size != "":
                d.append("serverdisk.serverdisk_size =" + serverdisk_size + "")
            if serverdisk_speed != "":
                d.append("serverdisk.serverdisk_speed =" + serverdisk_speed + "")
            if servercpu_type != "":
                d.append("servercpu.servercpu_type =" + servercpu_type + "")
            if servercpu_hz != "":
                d.append("servercpu.servercpu_hz =" + servercpu_hz + "")
            if servercpu_num != "":
                d.append("servercpu.servercpu_num =" + servercpu_num + "")
            if servermem_type != "":
                d.append("servermem.servermem_type =" + servermem_type + "")
            if servermem_hz != "":
                d.append("servermem.servermem_hz =" + servermem_hz + "")
            if servermem_size != "":
                d.append("servermem.servermem_size =" + servermem_size + "")
            if servermem_num != "":
                d.append("servermem.servermem_num =" + servermem_num + "")
            if servermemall_size != "":
                d.append("servermem.servermem_num*servermem.servermem_size =" + servermemall_size + "")
                
            #functions.set_echo(True)
            
            if len(d) == 0:
                if request.user.is_superuser:
                    data['total'] = self.servermodel.all().count()
                    data_info = {'data':self.servermodel.all().order_by(order_by).limit(pageSize).offset(content)}
                else:
                    data['total'] = self.servermodel.filter(self.serversystemmodel.c.server_id ==self.servermodel.c.id
                                                            ).filter(
                                                                self.serversystemmodel.c.opersgroupproject_id == self.opersgroupprojectmodel.c.id
                                                                     ).filter(
                                                                         and_(
                                                                             str(request.user) + " in (opersgroupproject.ogproject_user, \
                                                                             opersgroupproject.ogproject_opuser)")
                                                                         ).count()
                    data_info = {'data':self.servermodel.filter(self.serversystemmodel.c.server_id ==self.servermodel.c.id
                                                            ).filter(
                                                                self.serversystemmodel.c.opersgroupproject_id == self.opersgroupprojectmodel.c.id
                                                                     ).filter(
                                                                         and_(
                                                                             str(request.user) + " in (opersgroupproject.ogproject_user, \
                                                                             opersgroupproject.ogproject_opuser)")
                                                                         ).order_by(order_by).limit(pageSize).offset(content)}                    
            else:
                #functions.set_echo(True)
                key = (" " + r1 + " ").join(d)
                if r1 == 'and':
                    if request.user.is_superuser:
                        #from_obj=self.servermodel.c.server_sn
                        data['total'] =  do_(select(['count(distinct server.server_sn)'],and_(
                                    self.servermodel.c.rackspace_id == self.rackspacemodel.c.id,
                                    self.computerroommodel.c.id == self.rackspacemodel.c.computerroom_id,
                                    self.serverdiskmodel.c.server_id==self.servermodel.c.id,
                                    self.servermemmodel.c.server_id==self.servermodel.c.id,
                                    self.servercpumodel.c.server_id==self.servermodel.c.id,
                                    self.servermodel.c.hardware_id==self.hardwaremodel.c.id,
                                    self.hardwaremanufacturermodel.c.id == self.hardwaremodel.c.hardwaremanufacturer_id,
                                    key
                                    ))).scalar()
        
                        data_info = {'data':self.servermodel.filter(
                            self.servermodel.c.rackspace_id == self.rackspacemodel.c.id
                            ).filter(self.computerroommodel.c.id == self.rackspacemodel.c.computerroom_id
                                     ).filter(self.serverdiskmodel.c.server_id==self.servermodel.c.id
                                              ).filter(self.servermemmodel.c.server_id==self.servermodel.c.id
                                                  ).filter(self.servercpumodel.c.server_id==self.servermodel.c.id
                                                      ).filter(self.servermodel.c.hardware_id==self.hardwaremodel.c.id
                                                        ).filter(self.hardwaremanufacturermodel.c.id == self.hardwaremodel.c.hardwaremanufacturer_id,
                                                                 ).filter(and_(key)).group_by(self.servermodel.c.server_sn
                                                          ).limit(pageSize).offset(content)
                                                 }
                    else:
                        data['total'] =  do_(select(['count(distinct server.server_sn)'],and_(
                                    self.servermodel.c.rackspace_id == self.rackspacemodel.c.id,
                                    self.computerroommodel.c.id == self.rackspacemodel.c.computerroom_id,
                                    self.serverdiskmodel.c.server_id==self.servermodel.c.id,
                                    self.servermemmodel.c.server_id==self.servermodel.c.id,
                                    self.servercpumodel.c.server_id==self.servermodel.c.id,
                                    self.servermodel.c.hardware_id==self.hardwaremodel.c.id,
                                    self.serversystemmodel.c.server_id ==self.servermodel.c.id,
                                    self.hardwaremanufacturermodel.c.id == self.hardwaremodel.c.hardwaremanufacturer_id,
                                    self.serversystemmodel.c.opersgroupproject_id == self.opersgroupprojectmodel.c.id,
                                    str(request.user) + " in (opersgroupproject.ogproject_user,opersgroupproject.ogproject_opuser)",
                                    key
                                    ))).scalar()
        
                        data_info = {'data':self.servermodel.filter(
                            self.servermodel.c.rackspace_id == self.rackspacemodel.c.id
                            ).filter(self.computerroommodel.c.id == self.rackspacemodel.c.computerroom_id
                                     ).filter(self.serverdiskmodel.c.server_id==self.servermodel.c.id
                                              ).filter(self.servermemmodel.c.server_id==self.servermodel.c.id
                                                  ).filter(self.servercpumodel.c.server_id==self.servermodel.c.id
                                                      ).filter(self.servermodel.c.hardware_id==self.hardwaremodel.c.id
                                                        ).filter(self.hardwaremanufacturermodel.c.id == self.hardwaremodel.c.hardwaremanufacturer_id,
                                                                 ).filter(and_(key)
                                                                 ).filter(self.serversystemmodel.c.server_id ==self.servermodel.c.id
                                                            ).filter(
                                                                self.serversystemmodel.c.opersgroupproject_id == self.opersgroupprojectmodel.c.id
                                                                     ).filter(
                                                                         and_(
                                                                             str(request.user) + " in (opersgroupproject.ogproject_user, \
                                                                             opersgroupproject.ogproject_opuser)")
                                                                         ).group_by(self.servermodel.c.server_sn
                                                          ).limit(pageSize).offset(content)
                                                 }         
                else:
                    if request.user.is_superuser:
                        #from_obj=self.servermodel.c.server_sn
                        data['total'] =  do_(select(['count(distinct server.server_sn)'],and_(
                                    self.servermodel.c.rackspace_id == self.rackspacemodel.c.id,
                                    self.computerroommodel.c.id == self.rackspacemodel.c.computerroom_id,
                                    self.serverdiskmodel.c.server_id==self.servermodel.c.id,
                                    self.servermemmodel.c.server_id==self.servermodel.c.id,
                                    self.servercpumodel.c.server_id==self.servermodel.c.id,
                                    self.servermodel.c.hardware_id==self.hardwaremodel.c.id,
                                    self.hardwaremanufacturermodel.c.id == self.hardwaremodel.c.hardwaremanufacturer_id,
                                    "(" +key + ")"
                                    ))).scalar()
        
                        data_info = {'data':self.servermodel.filter(
                            self.servermodel.c.rackspace_id == self.rackspacemodel.c.id
                            ).filter(self.computerroommodel.c.id == self.rackspacemodel.c.computerroom_id
                                     ).filter(self.serverdiskmodel.c.server_id==self.servermodel.c.id
                                              ).filter(self.servermemmodel.c.server_id==self.servermodel.c.id
                                                  ).filter(self.servercpumodel.c.server_id==self.servermodel.c.id
                                                      ).filter(self.servermodel.c.hardware_id==self.hardwaremodel.c.id
                                                        ).filter(self.hardwaremanufacturermodel.c.id == self.hardwaremodel.c.hardwaremanufacturer_id,
                                                                 ).filter(and_("(" +key + ")")).group_by(self.servermodel.c.server_sn
                                                          ).limit(pageSize).offset(content)
                                                 }
                    else:
                        data['total'] =  do_(select(['count(distinct server.server_sn)'],and_(
                                    self.servermodel.c.rackspace_id == self.rackspacemodel.c.id,
                                    self.computerroommodel.c.id == self.rackspacemodel.c.computerroom_id,
                                    self.serverdiskmodel.c.server_id==self.servermodel.c.id,
                                    self.servermemmodel.c.server_id==self.servermodel.c.id,
                                    self.servercpumodel.c.server_id==self.servermodel.c.id,
                                    self.servermodel.c.hardware_id==self.hardwaremodel.c.id,
                                    self.hardwaremanufacturermodel.c.id == self.hardwaremodel.c.hardwaremanufacturer_id,
                                    self.serversystemmodel.c.server_id ==self.servermodel.c.id,
                                    self.serversystemmodel.c.opersgroupproject_id == self.opersgroupprojectmodel.c.id,
                                    str(request.user) + " in (opersgroupproject.ogproject_user,opersgroupproject.ogproject_opuser)", 
                                    "(" +key + ")"
                                    ))).scalar()
        
                        data_info = {'data':self.servermodel.filter(
                            self.servermodel.c.rackspace_id == self.rackspacemodel.c.id
                            ).filter(self.computerroommodel.c.id == self.rackspacemodel.c.computerroom_id
                                     ).filter(self.serverdiskmodel.c.server_id==self.servermodel.c.id
                                              ).filter(self.servermemmodel.c.server_id==self.servermodel.c.id
                                                  ).filter(self.servercpumodel.c.server_id==self.servermodel.c.id
                                                      ).filter(self.servermodel.c.hardware_id==self.hardwaremodel.c.id
                                                        ).filter(self.hardwaremanufacturermodel.c.id == self.hardwaremodel.c.hardwaremanufacturer_id,
                                                                 ).filter(and_("(" +key + ")")
                                                                 ).filter(self.serversystemmodel.c.server_id ==self.servermodel.c.id
                                                            ).filter(
                                                                self.serversystemmodel.c.opersgroupproject_id == self.opersgroupprojectmodel.c.id
                                                                     ).filter(
                                                                         and_(
                                                                             str(request.user) + " in (opersgroupproject.ogproject_user, \
                                                                             opersgroupproject.ogproject_opuser)")
                                                                         ).group_by(self.servermodel.c.server_sn
                                                          ).limit(pageSize).offset(content)
                                                 }
                
        #未知的错误
        else:
            return json({'msg':"非法"})
            

        for i in data_info['data']:

            ormdata_system = functions.get_object(self.serversystemmodel, i.id, cache=True, use_local=True)

            if ormdata_system.department_id == None:
                department_name = ''
            else:
                department_name = ormdata_system.department_id.department_name
                
            if ormdata_system.opersgroup_id == None:
                opersgroup_name = ''
            else:
                opersgroup_name = ormdata_system.opersgroup_id.opersgroup_name
    
            if ormdata_system.opersgroupproject_id == None:
                opersgroupproject_name = ''
            else:
                opersgroupproject_name = ormdata_system.opersgroupproject_id.ogproject_name
                
            if ormdata_system.applicationtype_id == None:
                applicationtype_name = ''
            else:
                applicationtype_name = ormdata_system.applicationtype_id.applicationtype_name
                
            data['rows'].append({"id":i.id,
                                 "computerroom_id":i.rackspace_id.computerroom_id.cr_name,
                                 "rackspace_id":i.rackspace_id.rackspace_name,
                                 "server_manufacturer":i.hardware_id.hardwaremanufacturer_id.hardwaremanufacturer_name,
                                 "server_type":i.hardware_id.hardware_name,
                                 "server_sn":i.server_sn,
                                 "server_asset_tag":i.server_asset_tag,
                                 "hardwarestatus_id":i.hardwarestatus_id.hardwarestatus_name,
                                 "department_name":department_name,
                                 "opersgroupproject_name":opersgroupproject_name,
                                 "applicationtype_name":applicationtype_name
                                 })

        #返回json串
        return json(data)
